package com.glwan.fixedassetapp.utils;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.glwan.fixedassetapp.common.DatabaseConstant;

import java.util.List;
import java.util.regex.Pattern;

public class DatabaseUtil extends SQLiteOpenHelper {

    private static final String TAG = "DatabaseUtil";


//    private Activity activity;

    private static DatabaseUtil databaseUtil;
    private Pattern pattern = Pattern.compile("^[A-Z]$");

    private DatabaseUtil(Activity activity) {
        super(activity, DatabaseConstant.DATABASE_NAME, null, 3);
    }

    public static DatabaseUtil getInstance(Activity activity) {

        if (databaseUtil == null) {
            databaseUtil = new DatabaseUtil(activity);
        }
//        databaseUtil.activity = activity;
        return databaseUtil;
    }

    public static void dropDataBase(String tableName) {
        SQLiteDatabase db = databaseUtil.getSQLiteDatabase(true);
        db.execSQL("DROP TABLE IF EXISTS " + tableName);
    }

    /**
     * 关闭SQLiteDatabase
     *
     * @param write 是否为写入的SQLiteDatabase
     */
    public static void closeSQLiteDatabase(boolean write) {
        databaseUtil.getSQLiteDatabase(write).close();
    }


//    private static final String TAG = "MyDatabaseUtils";
//
//    private static final String DATABASE_NAME = "Glwan_faa.db";
//
//    public static void createDataBase(Activity activity) {
//        boolean dbExist = new File(DATABASE_NAME).exists();
//        if (dbExist) {
//            dbExist = true;
//        } else {
//            dbExist = false;
//        }
//        if (dbExist) {
//            return;
//        }
//        SQLiteDatabase db = SQLiteDatabase.
//        db.execSQL();
//    }

    /**
     * 单条插入
     *
     * @param tableName 表名称
     * @param values    插入的key-value
     */
    public static void singleInsert(String tableName, ContentValues values) {
        try {
            SQLiteDatabase writableDatabase = databaseUtil.getSQLiteDatabase(true);
            writableDatabase.insert(tableName, null, values);
            writableDatabase.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量插入
     *
     * @param tableName      表名称
     * @param columnNameList 字段list
     * @param valueList      值list
     */
    public static void batchInsert(String tableName, List<String> columnNameList, List<List<String>> valueList) {
        SQLiteDatabase writableDatabase = databaseUtil.getSQLiteDatabase(true);
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(tableName).append(" ");

        //拼接columnName
        StringBuilder columnNameStringBuilder = new StringBuilder();
        columnNameStringBuilder.append("(");
        for (int i = 0; i < columnNameList.size(); i++) {
            String columnName = (columnNameList.get(i));
            columnNameStringBuilder.append("'").append(columnName).append("'");
            if (!(i == columnNameList.size() - 1)) {
                columnNameStringBuilder.append(",");
            }
        }
        columnNameStringBuilder.append(")");
        sql.append(columnNameStringBuilder.toString()).append(" VALUES ");

        //拼接values
        StringBuilder valuesStringBuilder = new StringBuilder();
        for (int i = 0; i < valueList.size(); i++) {
            List<String> valuesItem = valueList.get(i);
            valuesStringBuilder.append("(");
            for (int j = 0; j < valuesItem.size(); j++) {
                String value = valuesItem.get(j);
                valuesStringBuilder.append("'").append(value).append("'");
                if (!(j == valuesItem.size() - 1)) {
                    valuesStringBuilder.append(",");
                }
            }
            valuesStringBuilder.append(")");
            if (!(i == valueList.size() - 1)) {
                valuesStringBuilder.append(",");
            }
        }
        sql.append(valuesStringBuilder.toString()).append(";");

        //执行sql;
        writableDatabase.execSQL(sql.toString());
    }
//

    /**
     * 更新表数据
     *
     * @param tableName   表名称
     * @param values      值key-value
     * @param whereClause 条件参数及占位符
     * @param whereArgs   参数值
     * @return 更新行数
     */
    public static int updateData(String tableName, ContentValues values, String whereClause, String[] whereArgs) {
        int totalRaw;
        SQLiteDatabase db = databaseUtil.getSQLiteDatabase(true);
        db.beginTransaction();
        try {
            totalRaw = databaseUtil.getSQLiteDatabase(true).update(tableName, values, whereClause, whereArgs);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();

        }
        return totalRaw;

//        db.close();
    }

    /**
     * 删除表数据
     *
     * @param tableName   表名称
     * @param whereClause 条件参数及占位符
     * @param whereArgs   参数值
     * @return 删除条目数
     */
    public static int deleteData(String tableName, String whereClause, String[] whereArgs) {
        return databaseUtil.getSQLiteDatabase(true).delete(tableName, whereClause, whereArgs);
    }

    /**
     * 删除表的所有数据
     *
     * @param tableName 表名称
     */
    public static void deleteAllData(String tableName) {
        databaseUtil.getSQLiteDatabase(true).delete(tableName, null, null);
    }

    /**
     * 查询固定行数的数据，并根据orderBy，进行排序
     *
     * @param tableName     表名称
     * @param columns       字段名称数组
     * @param selection     参数及占位符
     * @param selectionArgs 参数值
     * @param orderBy       排序字段
     * @param groupBy       分组字段
     * @param having        分组筛选
     * @param limit         返回行数
     * @return Cursor
     */
    public static Cursor querySQL(String tableName, String[] columns, String selection, String[] selectionArgs, String orderBy, String groupBy, String having, String limit) {
        return query(false, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * 普通条件查询
     *
     * @param tableName     表名称
     * @param columns       字段名称数组
     * @param selection     参数及占位符
     * @param selectionArgs 参数值
     * @return Cursor
     */
    public static Cursor querySQL(String tableName, String[] columns, String selection, String[] selectionArgs) {
        return query(false, tableName, columns, selection, selectionArgs, null, null, null, null);
    }

    /**
     * 分页查询
     *
     * @param tableName     表名称
     * @param columns       字段名称数组
     * @param selection     参数及占位符
     * @param selectionArgs 参数值
     * @param limit         返回行数
     * @return Cursor
     */
    public static Cursor querySQL(String tableName, String[] columns, String selection, String[] selectionArgs, String orderBy, String limit) {
        return query(false, tableName, columns, selection, selectionArgs, orderBy, null, null, limit);
    }

    /**
     * 去重查询
     *
     * @param tableName     表名称
     * @param columns       字段名称数组
     * @param selection     参数及占位符
     * @param selectionArgs 参数值
     * @return Cursor
     */
    public static Cursor queryDistinctSQL(String tableName, String[] columns, String selection, String[] selectionArgs) {
        return query(true, tableName, columns, selection, selectionArgs, null, null, null, null);
    }

    public static Cursor query(boolean distinct, String tableName, String[] columns, String selection, String[] selectionArgs, String orderBy, String groupBy, String having, String limit) {
        return databaseUtil.getSQLiteDatabase(false).query(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * 自定义sql
     *
     * @param sql sql语句
     */
    public static void execSQL(String sql) {
        databaseUtil.getSQLiteDatabase(true).execSQL(sql);
    }

    /**
     * 自定义sql
     *
     * @param sql      sql语句
     * @param bindArgs 参数占位符值
     */
    public static void execSQL(String sql, String[] bindArgs) {
        databaseUtil.getSQLiteDatabase(true).execSQL(sql, bindArgs);
    }

    /**
     * 自定义查询sql
     *
     * @param sql           sql
     * @param selectionArgs 参数占位值
     * @return Cursor
     */
    public static Cursor rawQuery(String sql, String[] selectionArgs) {
        return databaseUtil.getSQLiteDatabase(false).rawQuery(sql, selectionArgs);
    }

    /**
     * 对象参数名转换字段
     *
     * @param s
     * @return
     */
    public static String convertColumn(String s) {
        char[] chars = s.toCharArray();

        StringBuilder sb = new StringBuilder();
        for (char c : chars) {
            if (c <= 90 && c >= 65) {
                sb.append("_").append(c);
                continue;
            }
            sb.append(c);
        }
        return sb.toString().toUpperCase();
    }

    /**
     * 字段转换对象参数名
     *
     * @param s
     * @return
     */
    public static String convertName(String s) {
        StringBuilder result = new StringBuilder();
        boolean capitalizeNext = false;

        for (int i = 0; i < s.length(); i++) {
            char currentChar = s.charAt(i);

            if (currentChar == '_') {
                capitalizeNext = true;
            } else {
                if (capitalizeNext) {
                    result.append(Character.toUpperCase(currentChar));
                    capitalizeNext = false;
                } else {
                    result.append(Character.toLowerCase(currentChar));
                }
            }
        }

        return result.toString();
    }

    /**
     * 获取 SQLiteDatabase对象
     *
     * @param write 是否为写入
     * @return write=true时，获取getWritableDatabase。write=false时，获取getReadableDatabase
     */
    private SQLiteDatabase getSQLiteDatabase(boolean write) {
        return write ? databaseUtil.getWritableDatabase() : databaseUtil.getReadableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //初始化sql
        String[] createSQLArray = {DatabaseConstant.INVENTORY_TASK_SQL, DatabaseConstant.INVENTORY_TASK_ITEM_SQL, DatabaseConstant.generateCreateTableSQL()};

        String dropSql = "DROP TABLE IF EXISTS ";
        sqLiteDatabase.execSQL(dropSql + DatabaseConstant.INVENTORY_TASK);
        sqLiteDatabase.execSQL(dropSql + DatabaseConstant.INVENTORY_TASK_ITEM);
        sqLiteDatabase.execSQL(dropSql + DatabaseConstant.OFFLINE_INVENTORY_TASK_ITEM);

        for (String createSQL : createSQLArray) {
            sqLiteDatabase.execSQL(createSQL);
            Log.i(TAG, createSQL);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

}
